   CREATE FUNCTION dbo.[ufnDelimitedTagToTable]
(
@List varchar(max), @Delimiter varchar(10)
)
RETURNS @Ids TABLE
(Id nvarchar(250)) AS
BEGIN
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @List = LTRIM(RTRIM(@List)) + @Delimiter
SET @pos = CHARINDEX(@Delimiter, @List, 1)
WHILE @pos > 0
    BEGIN
    SET @list1 = LTRIM(RTRIM(LEFT(@List, @pos - 1)))
    IF @list1 <> ''
        INSERT INTO @Ids(Id) VALUES(@list1)
    SET @List = SUBSTRING(@List, @pos+1, LEN(@List))
    SET @pos = CHARINDEX(@Delimiter, @list, 1)
END
RETURN 
END

   go
ALTER PROC spcmsOrder_FilterAdvance    
@CustomerId int,    
@PaymentMethod nvarchar(128) = null,    
@AccountCode nvarchar(128) = null,    
@OrderBy nvarchar(128) = null,    
@SystemStatus nvarchar(128) = null,  
@OrderTransactionStatus nvarchar(128) = null,
@StatusNotIn nvarchar(256) = null  
AS    
BEGIN    
 DECLARE @sql nvarchar(max);    
 DECLARE @order nvarchar(128) = 'Id desc';    
 SET @sql = 'SELECT *,(SELECT top 1 SystemStatusText FROM cmsSytemStatus WHERE SystemStatus = cmsOrders.SystemStatus) AS SystemStatusText,  
       (SELECT top 1 TransactionStatus FROM cmsTransactionStatus WHERE TransactionStatusCode = cmsOrders.TransactionOrderStatus) AS TransactionStatusText,  
       (SELECT top 1 PaymentText FROM cmsPaymentMethod WHERE PaymentCode = cmsOrders.PaymentMethod) AS PaymentText  
       FROM cmsOrders WHERE 1 = 1 ';    
 IF @CustomerId > 0    
  SET @sql += ' AND CustomerId = ' + CONVERT(nvarchar,@CustomerId) + '';    
 --ELSE IF @CustomerId = -1    
 --SET @sql += ' AND (CustomerId EXISTS(SELECT Id from cmsCustomer where IsVangLai = 0))';    
 IF @PaymentMethod <> ''    
  SET @sql += ' AND PaymentMethod = ''' + @PaymentMethod + '''';    
 IF @AccountCode <> ''     
  SET @sql += ' AND AccountCode = ''' + @AccountCode + '''';    
 IF @SystemStatus <> '' 
  SET @sql += ' AND SystemStatus = ''' + @SystemStatus + '''';    
 IF @OrderTransactionStatus <> ''   
  SET @sql += ' AND TransactionOrderStatus = ''' + @OrderTransactionStatus + '''';    
 IF @OrderBy <> ''    
  SET @order =  @OrderBy;    
 SET @sql += ' ORDER BY ' + @order;    
 print(@sql);    
 Exec(@sql);    
END

go
